SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'REGION' ROLLUP_TYPE, B.REGION 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.REGION
			FROM	MIS.ACC_RAW
			 B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT 
GROUP	BY REGION,YR, MN, DY
ORDER	BY REGION,YR, MN, DY
;

SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'MARKET' ROLLUP_TYPE, B.MARKET 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.MARKET
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT 
GROUP	BY MARKET,YR, MN, DY
ORDER	BY MARKET,YR, MN, DY
;


SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'AREA_MGR' ROLLUP_TYPE,B.AREA_MGR 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.AREA_MGR
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT 
GROUP	BY AREA_MGR,YR, MN, DY
ORDER	BY AREA_MGR,YR, MN, DY
;

SELECT YR, MN, DY,
SUM(IPTV_IN) IPTV_IN,   SUM(IPTV_OUT) IPTV_OUT,   SUM(IPTV_NET) IPTV_NET,
SUM(HSIA_IN) HSIA_IN,   SUM(HSIA_OUT) HSIA_OUT,   SUM(HSIA_NET) HSIA_NET,
SUM(VOIP_IN) VOIP_IN,   SUM(VOIP_OUT) VOIP_OUT,   SUM(VOIP_NET) VOIP_NET,
'STATE' ROLLUP_TYPE,  STATE
 FROM
(
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'TRAC_ID' ROLLUP_TYPE, SUBSTR(TRAC_ID,1,2) STATE
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.TRAC_ID 
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON C.TRAC_ID=B.TRAC_ID	 
		) 
		B 
	ON	A.DT=B.START_DT 
GROUP	BY STATE,YR, MN, DY
) AS 
TRAC_ID_ROLLUP
GROUP	BY STATE,YR, MN, DY
ORDER	BY STATE,YR, MN, DY
;

SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'ACCT_MGR' ROLLUP_TYPE, B.ACCT_MGR 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.ACCT_MGR
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT 
GROUP	BY ACCT_MGR,YR, MN, DY
ORDER	BY ACCT_MGR,YR, MN, DY
;
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'PORTFOLIO' ROLLUP_TYPE, B.PORTFOLIO 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.PORTFOLIO
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) B 
	ON	A.DT=B.START_DT 
GROUP	BY PORTFOLIO,YR, MN, DY
ORDER	BY PORTFOLIO,YR, MN, DY
;

;
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY, 
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,
'TRAC_ID' ROLLUP_TYPE, B.TRAC_ID 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.PRODUCT, B.DIRECTION, B.START_DT, C.TRAC_ID
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON C.TRAC_ID=B.TRAC_ID	 
		) 
		B 
	ON	A.DT=B.START_DT 
GROUP	BY TRAC_ID,YR, MN, DY
ORDER	BY TRAC_ID,YR, MN, DY